﻿using CBS.Model.ManageModel;
using Microsoft.VisualBasic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using static System.Net.Mime.MediaTypeNames;

namespace CBS.Common.Office
{
    public class ExcelHelper
    {

        public ExcelHelper() { }

        /// <summary>
        /// 文件流初始化对象
        /// </summary>
        /// <param name="stream"></param>
        public ExcelHelper(Stream stream)
        {
            _IWorkbook = CreateWorkbook(stream);
        }

        /// <summary>
        /// 传入文件名
        /// </summary>
        /// <param name="fileName"></param>
        public ExcelHelper(string fileName)
        {
            using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                _IWorkbook = CreateWorkbook(fileStream);
            }
        }

        /// <summary>
        /// 工作薄
        /// </summary>
        private IWorkbook _IWorkbook;

        /// <summary>
        /// 创建工作簿对象
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        private IWorkbook CreateWorkbook(Stream stream)
        {
            try
            {
                return new XSSFWorkbook(stream); //07
            }
            catch
            {
                return new HSSFWorkbook(stream); //03
            }

        }

        public DataTable ExportExcelToDataTableEveryCell()
        {
            return ExportToDataTableEveryCell(_IWorkbook.GetSheetAt(0));
        }
        private DataTable ExportToDataTableEveryCell(ISheet sheet)
        {
            DataTable dt = new DataTable();

            //默认，第一行是字段
            IRow headRow = sheet.GetRow(0);

            //设置datatable字段
            for (int i = headRow.FirstCellNum, len = headRow.LastCellNum; i < len; i++)
            {
                dt.Columns.Add(headRow.Cells[i].StringCellValue);
            }
            //遍历数据行
            for (int i = (sheet.FirstRowNum + 1), len = sheet.LastRowNum + 1; i < len; i++)
            {
                IRow tempRow = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                if (tempRow == null)
                {
                    continue;
                }

                bool isNull = true;
                //遍历一行的每一个单元格
                for (int j = 0; (j < tempRow.LastCellNum && j < headRow.LastCellNum); j++)
                {

                    ICell cell = tempRow.GetCell(j);

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.String:
                                dataRow[j] = cell.StringCellValue;
                                break;
                            case CellType.Numeric:
                                dataRow[j] = cell.NumericCellValue;
                                break;
                            case CellType.Boolean:
                                dataRow[j] = cell.BooleanCellValue;
                                break;
                            case CellType.Blank:
                                dataRow[j] = cell.StringCellValue;
                                break;
                            case CellType.Error:
                                dataRow[j] = cell.ErrorCellValue;
                                break;
                            case CellType.Unknown:
                            default:
                                dataRow[j] = cell.ToString();
                                break;
                        }

                        if (!string.IsNullOrWhiteSpace(cell.ToString()))
                        {
                            isNull = false;
                        }
                    }
                }

                if (!isNull)
                {
                    dt.Rows.Add(dataRow);
                }
            }
            return dt;
        }

        /// <summary>
        /// 把Sheet中的数据转换为DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private DataTable ExportToDataTable(ISheet sheet)
        {
            DataTable dt = new DataTable();

            //默认，第一行是字段
            IRow headRow = sheet.GetRow(0);

            //设置datatable字段
            for (int i = headRow.FirstCellNum, len = headRow.LastCellNum; i < len; i++)
            {
                dt.Columns.Add(headRow.Cells[i].StringCellValue);
            }
            //遍历数据行
            for (int i = (sheet.FirstRowNum + 1), len = sheet.LastRowNum + 1; i < len; i++)
            {
                IRow tempRow = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                //遍历一行的每一个单元格
                for (int r = 0, j = tempRow.FirstCellNum, len2 = tempRow.LastCellNum; j < len2; j++, r++)
                {

                    ICell cell = tempRow.GetCell(j);

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.String:
                                dataRow[r] = cell.StringCellValue;
                                break;
                            case CellType.Numeric:
                                dataRow[r] = cell.NumericCellValue;
                                break;
                            case CellType.Boolean:
                                dataRow[r] = cell.BooleanCellValue;
                                break;
                            case CellType.Blank: //空白
                                dataRow[r] = "";
                                break;
                            default:
                                dataRow[r] = "ERROR";
                                break;
                        }
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// Sheet中的数据转换为List集合
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        private IList<T> ExportToList<T>(ISheet sheet, string[] fields) where T : class, new()
        {
            IList<T> list = new List<T>();

            //遍历每一行数据
            for (int i = sheet.FirstRowNum + 1, len = sheet.LastRowNum + 1; i < len; i++)
            {
                T t = new T();
                IRow row = sheet.GetRow(i);

                for (int j = 0, len2 = fields.Length; j < len2; j++)
                {
                    ICell cell = row.GetCell(j);
                    object cellValue = null;

                    switch (cell.CellType)
                    {
                        case CellType.String: //文本
                            cellValue = cell.StringCellValue;
                            break;
                        case CellType.Numeric: //数值
                            cellValue = Convert.ToInt32(cell.NumericCellValue);//Double转换为int
                            break;
                        case CellType.Boolean: //bool
                            cellValue = cell.BooleanCellValue;
                            break;
                        case CellType.Blank: //空白
                            cellValue = "";
                            break;
                        default:
                            cellValue = "ERROR";
                            break;
                    }

                    typeof(T).GetProperty(fields[j]).SetValue(t, cellValue, null);
                }
                list.Add(t);
            }

            return list;
        }

        /// <summary>
        /// 获取第一个Sheet的第X行，第Y列的值。起始点为1
        /// </summary>
        /// <param name="X">行</param>
        /// <param name="Y">列</param>
        /// <returns></returns>
        public string GetCellValue(int X, int Y)
        {
            ISheet sheet = _IWorkbook.GetSheetAt(0);

            IRow row = sheet.GetRow(X - 1);

            return row.GetCell(Y - 1).ToString();
        }

        /// <summary>
        /// 获取一行的所有数据
        /// </summary>
        /// <param name="X">第x行</param>
        /// <returns></returns>
        public string[] GetCells(int X)
        {
            List<string> list = new List<string>();

            ISheet sheet = _IWorkbook.GetSheetAt(0);

            IRow row = sheet.GetRow(X - 1);

            for (int i = 0, len = row.LastCellNum; i < len; i++)
            {
                list.Add(row.GetCell(i).StringCellValue);//这里没有考虑数据格式转换，会出现bug
            }
            return list.ToArray();
        }

        /// <summary>
        /// 第一个Sheet数据，转换为DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ExportExcelToDataTable()
        {
            return ExportToDataTable(_IWorkbook.GetSheetAt(0));
        }

        /// <summary>
        /// 第sheetIndex表数据，转换为DataTable
        /// </summary>
        /// <param name="sheetIndex">第几个Sheet，从1开始</param>
        /// <returns></returns>
        public DataTable ExportExcelToDataTable(int sheetIndex)
        {
            return ExportToDataTable(_IWorkbook.GetSheetAt(sheetIndex - 1));
        }


        /// <summary>
        /// Excel中默认第一张Sheet导出到集合
        /// </summary>
        /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param>
        /// <returns></returns>
        public IList<T> ExcelToList<T>(string[] fields) where T : class, new()
        {
            return ExportToList<T>(_IWorkbook.GetSheetAt(0), fields);
        }

        /// <summary>
        /// Excel中指定的Sheet导出到集合
        /// </summary>
        /// <param name="sheetIndex">第几张Sheet,从1开始</param>
        /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param>
        /// <returns></returns>
        public IList<T> ExcelToList<T>(int sheetIndex, string[] fields) where T : class, new()
        {
            return ExportToList<T>(_IWorkbook.GetSheetAt(sheetIndex - 1), fields);
        }

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
        {
            using (MemoryStream ms = Export(dtSource, strHeaderText))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }
                ms.Flush();
                ms.Close();
                ms.Dispose();
            }
        }

        public static void WriteExcel(DataTable dt, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                // 写入到客户端  
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet("Sheet1") as XSSFSheet;
            #region 右击文件 属性信息
            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "tewuapple";
            //    workbook.DocumentSummaryInformation = dsi;
            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    si.Author = "sunzq"; //填加xls文件作者信息
            //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
            //    si.LastAuthor = "szq"; //填加xls文件最后保存者信息
            //    si.Comments = "说明信息"; //填加xls文件作者信息
            //    si.Title = "NPOI测试"; //填加xls文件标题信息
            //    si.Subject = "NPOI测试Demo";//填加文件主题信息
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}
            #endregion
            XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
            XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.Caption.ToString() == "" ? item.ColumnName.ToString() : item.Caption.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 1048575 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as XSSFSheet;
                    }
                    #region 表头及样式
                    {
                        //XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);
                        //XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        //headStyle.Alignment = HorizontalAlignment.Center;
                        //XSSFFont font = workbook.CreateFont() as XSSFFont;
                        //font.FontHeightInPoints = 20;
                        //font.Boldweight = 700;
                        //headStyle.SetFont(font);
                        //headerRow.GetCell(0).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                        XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        font.IsBold = true;
                        headStyle.IsLocked = true;
                        headStyle.SetFont(font);
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption == "" ? column.ColumnName : column.Caption);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        // sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
                    }
                    #endregion
                    rowIndex = 1;
                }
                #endregion

                #region 填充内容
                XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                            short int16V = 0;
                            if (short.TryParse(drValue, out int16V))
                            {
                                newCell.SetCellValue(int16V);
                            }
                            break;
                        case "System.Int32":
                            int intV = 0;
                            if (int.TryParse(drValue, out intV))
                            {
                                newCell.SetCellValue(intV);
                            }
                            break;
                        case "System.Int64":
                            newCell.SetCellValue(drValue);//为了防止数字过长以后，Excel把数字转换成科学计数法表示 如：201806019069 显示为 2.01806E+11
                            break;
                        case "System.Byte":
                            byte byteV = 0;
                            if (byte.TryParse(drValue, out byteV))
                            {
                                newCell.SetCellValue(byteV);
                            }
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                //ms.Flush();
                //ms.Position = 0;
                //ms.Seek(0, SeekOrigin.Begin);
                //ms.Dispose();
                //ms.Flush();
                return ms;
            }
        }

        public static MemoryStream ExportNotDisposeStream(DataTable dtSource, string strHeaderText)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet("Sheet1") as XSSFSheet;
            #region 右击文件 属性信息
            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "tewuapple";
            //    workbook.DocumentSummaryInformation = dsi;
            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    si.Author = "sunzq"; //填加xls文件作者信息
            //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
            //    si.LastAuthor = "szq"; //填加xls文件最后保存者信息
            //    si.Comments = "说明信息"; //填加xls文件作者信息
            //    si.Title = "NPOI测试"; //填加xls文件标题信息
            //    si.Subject = "NPOI测试Demo";//填加文件主题信息
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}
            #endregion
            XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
            XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.Caption.ToString() == "" ? item.ColumnName.ToString() : item.Caption.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 1048575 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as XSSFSheet;
                    }
                    #region 表头及样式
                    {
                        //XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);
                        //XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        //headStyle.Alignment = HorizontalAlignment.Center;
                        //XSSFFont font = workbook.CreateFont() as XSSFFont;
                        //font.FontHeightInPoints = 20;
                        //font.Boldweight = 700;
                        //headStyle.SetFont(font);
                        //headerRow.GetCell(0).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                        XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        font.IsBold = true;
                        headStyle.IsLocked = true;
                        headStyle.SetFont(font);
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption == "" ? column.ColumnName : column.Caption);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        // sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
                    }
                    #endregion
                    rowIndex = 1;
                }
                #endregion

                #region 填充内容
                XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Int64":
                            newCell.SetCellValue(drValue);//是为了防止数字过长以后，Excel把数字转换成科学计数法表示 如：201806019069 显示为 2.01806E+11
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                #endregion
                rowIndex++;
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);

            return ms;
        }


        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
        //{
        //    HttpContext curContext = HttpContext.Current;
        //    // 设置编码和附件格式
        //    curContext.Response.ContentType = "application/vnd.ms-excel";
        //    curContext.Response.ContentEncoding = Encoding.UTF8;
        //    curContext.Response.Charset = "";
        //    curContext.Response.AppendHeader("Content-Disposition",
        //        "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
        //    curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
        //    curContext.Response.End();
        //}


        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();
            XSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }
            XSSFSheet sheet = hssfworkbook.GetSheetAt(0) as XSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                XSSFCell cell = headerRow.GetCell(j) as XSSFCell;
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                XSSFRow row = sheet.GetRow(i) as XSSFRow;
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        public static DataTable ImportBase(string strFileName)
        {
            string filePostfixName = strFileName.Split('.')[1];
            return ImportBase(strFileName, filePostfixName);
        }

        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable ImportBase(string strFileName, string filePostfixName)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (filePostfixName == "xlsx") hssfworkbook = new XSSFWorkbook(file);
                else hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="filePostfixName"></param>
        /// <returns></returns>
        public static DataTable GetFirstRowData(string strFileName, string filePostfixName)
        {
            if (filePostfixName == "xlsx") return GetFirstRowDataXLSX(strFileName);
            else return GetFirstRowDataXLS(strFileName);
        }
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable GetFirstRowDataXLSX(string strFileName)
        {
            DataTable dt = new DataTable();
            DataRow dataRow = dt.NewRow();
            XSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }
            XSSFSheet sheet = hssfworkbook.GetSheetAt(0) as XSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
            int cellCount = headerRow.LastCellNum;
            XSSFRow row = sheet.GetRow(0) as XSSFRow;
            for (int j = 0; j < cellCount; j++)
            {
                XSSFCell cell = headerRow.GetCell(j) as XSSFCell;
                if (string.IsNullOrWhiteSpace(cell.ToString()))
                {
                    throw new Exception(string.Format("表头不允许为空，请确认修改或删除该列。"));
                }
                dt.Columns.Add(cell.ToString());
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }
            dt.Rows.Add(dataRow);
            return dt;
        }

        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable GetFirstRowDataXLS(string strFileName)
        {
            DataTable dt = new DataTable();
            DataRow dataRow = dt.NewRow();
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
            int cellCount = headerRow.LastCellNum;
            HSSFRow row = sheet.GetRow(0) as HSSFRow;
            for (int j = 0; j < cellCount; j++)
            {
                HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
                if (string.IsNullOrWhiteSpace(cell.ToString()))
                {
                    throw new Exception(string.Format("表头不允许为空，请确认修改或删除该列。"));
                }
                dt.Columns.Add(cell.ToString());
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }
            dt.Rows.Add(dataRow);
            return dt;
        }

        /// <summary>  
        /// 将DataTable数据导出到Excel文件中(xlsx)   解决单元格内字符串长度过长报错问题 
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="file"></param>  
        public static void TableToExcelForXLSX(DataTable dt, string file)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet("Sheet1");
            IDataFormat dataformat = xssfworkbook.CreateDataFormat();

            //表头  
            IRow row = sheet.CreateRow(0);
            List<int> stringDataFormatIndex = new List<int>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].Caption == "" ? dt.Columns[i].ColumnName : dt.Columns[i].Caption);

                //背景色
                ICellStyle style = xssfworkbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.FillForegroundColor = new XSSFColor(SixLabors.ImageSharp.Color.LightGray).Indexed;
                style.RightBorderColor = new XSSFColor(SixLabors.ImageSharp.Color.Black).Indexed;
                style.BottomBorderColor = new XSSFColor(SixLabors.ImageSharp.Color.Black).Indexed;

                if (dt.Columns[i].ColumnName == "cardNumber" || dt.Columns[i].Caption == "证件号")
                {

                    style.DataFormat = dataformat.GetFormat("text");
                    stringDataFormatIndex.Add(i);
                }

                //style.FillPattern = FillPattern.SolidForeground;
                //字体
                IFont font = xssfworkbook.CreateFont();
                //font.Boldweight = 700;
                font.IsBold = true;
                style.IsLocked = true;
                style.SetFont(font);

                cell.CellStyle = style;
            }

            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    if (stringDataFormatIndex.IndexOf(i) > 0)
                    {
                        cell.CellStyle.DataFormat = dataformat.GetFormat("text");
                    }
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>  
        /// 将DataTable数据导出到Excel文件中(xlsx)
        /// 1、解决单元格内字符串长度过长报错问题
        /// 2、包含下拉选项
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="file"></param>  
        public static void EmptyTableToExcelForXLSX(DataTable dt, string file, List<int> selectColumnIndex, List<List<string>> selectItems, int selectRowTotal)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet("Sheet1");
            IDataFormat dataformat = xssfworkbook.CreateDataFormat();

            //表头  
            IRow row = sheet.CreateRow(0);
            List<int> stringDataFormatIndex = new List<int>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].Caption == "" ? dt.Columns[i].ColumnName : dt.Columns[i].Caption);

                //背景色
                ICellStyle style = xssfworkbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.FillForegroundColor = new XSSFColor(SixLabors.ImageSharp.Color.LightGray).Indexed;
                style.RightBorderColor = new XSSFColor(SixLabors.ImageSharp.Color.Black).Indexed;
                style.BottomBorderColor = new XSSFColor(SixLabors.ImageSharp.Color.Black).Indexed;

                if (dt.Columns[i].ColumnName == "cardNumber" || dt.Columns[i].Caption == "证件号")
                {

                    style.DataFormat = dataformat.GetFormat("text");
                    stringDataFormatIndex.Add(i);
                }

                //style.FillPattern = FillPattern.SolidForeground;
                //字体
                IFont font = xssfworkbook.CreateFont();
                //font.Boldweight = 700;
                font.IsBold = true;
                style.IsLocked = true;
                style.SetFont(font);

                cell.CellStyle = style;
            }

            ////数据  
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    IRow row1 = sheet.CreateRow(i + 1);
            //    for (int j = 0; j < dt.Columns.Count; j++)
            //    {
            //        ICell cell = row1.CreateCell(j);
            //        if (stringDataFormatIndex.IndexOf(i) > 0)
            //        {
            //            cell.CellStyle.DataFormat = dataformat.GetFormat("text");
            //        }
            //        cell.SetCellValue(dt.Rows[i][j].ToString());
            //    }
            //}

            // 定义下拉列表的选项
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                for (int j = 0; j < selectColumnIndex.Count; j++)
                {
                    if (i != selectColumnIndex[j] - 1)
                    {
                        continue;
                    }

                    string[] categories = selectItems[j].ToArray();

                    #region HSSF
                    //var range = new CellRangeAddressList(1, selectRowTotal + 1, i, i);
                    //var dvConstraint = DVConstraint.CreateExplicitListConstraint(categories);
                    //var validation = new HSSFDataValidation(range, dvConstraint);
                    //// 添加数据验证到工作表
                    //sheet.AddValidationData(validation);
                    #endregion

                    #region XSSF


                    // 获取数据验证助手
                    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);

                    // 创建下拉列表约束
                    IDataValidationConstraint dvConstraint = dvHelper.CreateExplicitListConstraint(categories);
                    CellRangeAddressList addressList = new CellRangeAddressList(1, selectRowTotal + 1, i, i);

                    // 创建数据验证对象
                    IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);

                    // 设置数据验证的错误提示框属性
                    validation.ShowErrorBox = true;

                    // 将数据验证添加到工作表
                    sheet.AddValidationData(validation);

                    #endregion
                }

            }



            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        public static byte[] TableToStreamForXLSX(DataTable dt)
        {
            return TableToStreamForXLSX(dt, "Sheet1");
        }

        public static byte[] TableToStreamForXLSX(DataTable dt, string sheetName)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet(sheetName);

            //表头  
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].Caption == "" ? dt.Columns[i].ColumnName : dt.Columns[i].Caption);

                //背景色
                ICellStyle style = xssfworkbook.CreateCellStyle();
                style.FillForegroundColor = IndexedColors.Grey25Percent.Index;
                style.RightBorderColor = IndexedColors.Black.Index;
                style.BottomBorderColor = IndexedColors.Black.Index;
                style.FillPattern = FillPattern.SolidForeground;
                //字体
                IFont font = xssfworkbook.CreateFont();

                style.SetFont(font);

                cell.CellStyle = style;
            }

            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组  
            using (MemoryStream stream = new MemoryStream())
            {
                xssfworkbook.Write(stream);
                return stream.ToArray();
            }
        }

        /// <summary>  
        /// 获取单元格类型(xlsx)  
        /// </summary>  
        /// <param name="cell"></param>  
        /// <returns></returns>  
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }

        /// <summary>
        /// List集合转化为DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(IEnumerable<T> collection)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            //dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            foreach (PropertyInfo pi in props)
            {
                Type colType = pi.PropertyType;//得到属性的类型
                                               //如果属性为泛型类型
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                == typeof(Nullable<>)))
                {   //获取泛型类型的参数
                    colType = colType.GetGenericArguments()[0];
                }
                //将类型的属性名称与属性类型作为DataTable的列数据
                dt.Columns.Add(new DataColumn(pi.Name, colType));
            }

            if (collection.Count() > 0)
            {
                int count = collection.Count();
                for (int i = 0; i < count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null) == null ? DBNull.Value : pi.GetValue(collection.ElementAt(i), null); ;
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }

        public static DataTable LINQToDataTable<T>(IEnumerable<T> varlist, List<string> filedSetList = null)
        {   //定义要返回的DataTable对象
            DataTable dtReturn = new DataTable();
            // 保存列集合的属性信息数组
            PropertyInfo[] oProps = null;
            if (varlist == null) return dtReturn;//安全性检查
                                                 //循环遍历集合，使用反射获取类型的属性信息
            foreach (T rec in varlist)
            {
                //使用反射获取T类型的属性信息，返回一个PropertyInfo类型的集合
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    //循环PropertyInfo数组
                    foreach (PropertyInfo pi in oProps)
                    {
                        if (filedSetList != null && filedSetList.Count > 0)
                        {
                            if (!filedSetList.Contains(pi.Name)) continue;
                        }
                        Type colType = pi.PropertyType;//得到属性的类型
                                                       //如果属性为泛型类型
                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                        == typeof(Nullable<>)))
                        {   //获取泛型类型的参数
                            colType = colType.GetGenericArguments()[0];
                        }
                        //将类型的属性名称与属性类型作为DataTable的列数据
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }
                //新建一个用于添加到DataTable中的DataRow对象
                DataRow dr = dtReturn.NewRow();
                //循环遍历属性集合
                foreach (PropertyInfo pi in oProps)
                {   //为DataRow中的指定列赋值
                    dr[pi.Name] = pi.GetValue(rec, null) == null ?
                        DBNull.Value : pi.GetValue(rec, null);
                }
                //将具有结果值的DataRow添加到DataTable集合中
                dtReturn.Rows.Add(dr);
            }
            return dtReturn;//返回DataTable对象
        }

        public static DataTable LINQToDataTableOnType<T>(IEnumerable<T> varlist, Type type)
        {
            //定义要返回的DataTable对象
            DataTable dtReturn = new DataTable();
            if (varlist == null) return dtReturn;//安全性检查

            if (!type.IsAssignableFrom(varlist.FirstOrDefault().GetType()))
            {
                throw new Exception("集合参数必须是类型参数及其子类的集合。");
            }

            PropertyInfo[] oProps = type.GetProperties();
            string[] propName = oProps.Select(t => t.Name).ToArray();
            //循环PropertyInfo数组
            foreach (PropertyInfo pi in oProps)
            {
                Type colType = pi.PropertyType;//得到属性的类型
                                               //如果属性为泛型类型
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                == typeof(Nullable<>)))
                {   //获取泛型类型的参数
                    colType = colType.GetGenericArguments()[0];
                }
                //将类型的属性名称与属性类型作为DataTable的列数据
                dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
            }

            foreach (T item in varlist)//循环遍历集合，使用反射获取类型的属性信息
            {
                //新建一个用于添加到DataTable中的DataRow对象
                DataRow dr = dtReturn.NewRow();
                foreach (var pi in item.GetType().GetProperties())
                {
                    if (!propName.Contains(pi.Name))
                    {
                        continue;
                    }

                    //为DataRow中的指定列赋值
                    dr[pi.Name] = pi.GetValue(item, null) == null ?
                        DBNull.Value : pi.GetValue(item, null);
                }
                //将具有结果值的DataRow添加到DataTable集合中
                dtReturn.Rows.Add(dr);
            }

            return dtReturn;//返回DataTable对象
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="fromPath"></param>
        /// <param name="toPath"></param>
        public static void DealErrorInfo(string message, string fromPath, string toPath, string folderPath)
        {
            IWorkbook hssfworkbook;
            var ext = Path.GetExtension(fromPath).ToLower();
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(fromPath, FileMode.Open, FileAccess.Read))
                {
                    if (ext.Contains("xlsx"))
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                    else
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
            }
            catch
            {
                return;
            }
            if (Directory.Exists(folderPath) == false)//路径不存在则创建  
                Directory.CreateDirectory(folderPath);
            #endregion
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
            row.CreateCell(0).SetCellValue("错误信息");
            row.CreateCell(1).SetCellValue(message);

            // 写入到客户端  
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                hssfworkbook.Write(ms);
                using (FileStream fs = new FileStream(toPath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
                hssfworkbook = null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable ImportExcelFileReplaceAllBlank(string filePath)
        {
            IWorkbook hssfworkbook;
            var ext = Path.GetExtension(filePath).ToLower();
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (ext.Contains("xlsx"))
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                    else if (ext.Contains("xls"))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                    else
                    {
                        throw new Exception("模板不正确！");
                    }
                }
            }
            catch (Exception e)
            {
                //LogAsyncWriter.Default.Error(e, nameof(ExcelHelper));
                throw;
            }
            #endregion


            ISheet sheet = hssfworkbook.GetSheetAt(0);
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(2);
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = GetCellValue(row.GetCell(j)).Replace(" ", "");
                    }
                }

                table.Rows.Add(dataRow);
            }
            return table;
        }

        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

        /// <summary>
        /// List<T>导出到Excel文件
        /// </summary>
        /// <param name="dtSource">列表数据</param>
        /// <param name="strHeader">表头</param>
        /// <param name="strFileName">保存位置</param>
        public static void Export<T>(List<T> dtSource, Dictionary<string, string> strHeader, string strFileName)
        {
            using (MemoryStream ms = Export(dtSource, strHeader))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }
                ms.Flush();
                ms.Close();
                ms.Dispose();
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeader">表头</param>
        public static MemoryStream Export<T>(List<T> dtSource, Dictionary<string, string> strHeader)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet("Sheet1") as XSSFSheet;
            #region 右击文件 属性信息
            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "tewuapple";
            //    workbook.DocumentSummaryInformation = dsi;
            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    si.Author = "sunzq"; //填加xls文件作者信息
            //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
            //    si.LastAuthor = "szq"; //填加xls文件最后保存者信息
            //    si.Comments = "说明信息"; //填加xls文件作者信息
            //    si.Title = "NPOI测试"; //填加xls文件标题信息
            //    si.Subject = "NPOI测试Demo";//填加文件主题信息
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}
            #endregion
            XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
            XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            List<PropertyInfo> oProps = typeof(T).GetProperties().ToList();
            oProps.RemoveAll(t => !strHeader.ContainsKey(t.Name));

            //取得列宽
            int[] arrColWidth = new int[oProps.Count];
            for (int i = 0; i < arrColWidth.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(strHeader[oProps[i].Name]).Length;
            }

            for (int i = 0; i < dtSource.Count; i++)
            {
                for (int j = 0; j < arrColWidth.Length; j++)
                {
                    if (dtSource[i] == null || oProps[j].GetValue(dtSource[i]) == null)
                    {
                        continue;
                    }
                    int intTemp = Encoding.GetEncoding(936).GetBytes(oProps[j].GetValue(dtSource[i]).ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (T row in dtSource)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 1048575 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as XSSFSheet;
                    }
                    #region 表头及样式
                    {
                        //XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);
                        //XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        //headStyle.Alignment = HorizontalAlignment.Center;
                        //XSSFFont font = workbook.CreateFont() as XSSFFont;
                        //font.FontHeightInPoints = 20;
                        //font.Boldweight = 700;
                        //headStyle.SetFont(font);
                        //headerRow.GetCell(0).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                        XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        font.IsBold = true;
                        headStyle.IsLocked = true;
                        headStyle.SetFont(font);
                        for (int i = 0; i < arrColWidth.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(strHeader[oProps[i].Name]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                    }
                    #endregion
                    rowIndex = 1;
                }
                #endregion

                #region 填充内容
                XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
                for (int i = 0; i < arrColWidth.Length; i++)
                {


                    XSSFCell newCell = dataRow.CreateCell(i) as XSSFCell;
                    string drValue = oProps[i].GetValue(row) == null ? null : oProps[i].GetValue(row).ToString();

                    string type = oProps[i].PropertyType.ToString();
                    if ((oProps[i].PropertyType.IsGenericType)
                            && (oProps[i].PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {   //获取泛型类型的参数
                        type = oProps[i].PropertyType.GetGenericArguments()[0].ToString();
                    }

                    switch (type)
                    {
                        case "System.String"://字符串类型

                            if (!string.IsNullOrEmpty(drValue) && drValue.Length > 255)
                            {
                                newCell.SetCellValue(new XSSFRichTextString(drValue));
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                            }
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                            short int16V = 0;
                            if (short.TryParse(drValue, out int16V))
                            {
                                newCell.SetCellValue(int16V);
                            }
                            break;
                        case "System.Int32":
                            int intV = 0;
                            if (int.TryParse(drValue, out intV))
                            {
                                newCell.SetCellValue(intV);
                            }
                            break;
                        case "System.Int64":
                            newCell.SetCellValue(drValue);//为了防止数字过长以后，Excel把数字转换成科学计数法表示 如：201806019069 显示为 2.01806E+11
                            break;
                        case "System.Byte":
                            byte byteV = 0;
                            if (byte.TryParse(drValue, out byteV))
                            {
                                newCell.SetCellValue(byteV);
                            }
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                return ms;
            }
        }

        /// <summary>
        /// 检查Excel表格是否包含指定表头。要求导入设置每一列都包含在Excel中
        /// </summary>
        /// <param name="dt">根据导入的Excel数据第一列生成的DataTable</param>
        /// <param name="strStandardNames">指定要包含的表头字符串数组</param>
        /// <returns></returns>
        public static string ValidExcel(DataTable dt, string[] strStandardNames)
        {

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Trim();
            }
            //要求导入设置每一列都包含在Excel中
            for (int i = 0; i < strStandardNames.Length; i++)
            {
                if (!dt.Columns.Contains(strStandardNames[i]))
                {
                    return "导入的Excel表头内容有错误！第一行表头必须包含:" + string.Join(",", strStandardNames) + "。请使用正确的模板！";
                }
            }

            return "";
        }

        public static void GetImportTemplate(string filePath, List<ImportField> fields)
        {
            string dir = Directory.GetParent(filePath).FullName;
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }

            DataTable dt = new DataTable();

            foreach (var item in fields)
            {
                DataColumn dc = new DataColumn(item.Name)
                {
                    Caption = item.DisplayName
                };

                dt.Columns.Add(dc);
            }

            ExcelHelper.TableToExcelForXLSX(dt, filePath);
        }

        public static void GetImportTemplate(string filePath, List<ImportField> fields, List<int> selectColumnIndex, List<List<string>> selectItems, int selectRowTotal)
        {
            string dir = Directory.GetParent(filePath).FullName;
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }

            DataTable dt = new DataTable();

            foreach (var item in fields)
            {
                DataColumn dc = new DataColumn(item.Name)
                {
                    Caption = item.DisplayName
                };

                dt.Columns.Add(dc);
            }

            ExcelHelper.EmptyTableToExcelForXLSX(dt, filePath, selectColumnIndex, selectItems, selectRowTotal);
        }


    }
}
